package com.spi.soconversion.orders;

import java.sql.CallableStatement;
import java.sql.SQLException;
import java.sql.Statement;

import com.spi.soconversion.base.SOCException;
import com.spi.soconversion.util.DBConnection;
import com.spi.soconversion.util.ErrorConstants;
import com.spi.soconversion.util.ErrorMessageReader;
import com.spi.soconversion.util.QueryConstants;
import com.spi.soconversion.util.QueryReader;
import com.spi.soconversion.util.SOCConstants;
import com.spi.soconversion.util.SOCFileWriter;
import com.spi.soconversion.util.SOCUtility;

public class SOCFetchInstallBase {
	
	private String d_logFileName = null;
	private SOCFileWriter d_fileWriter = null;
	private CallableStatement cs = null;
	private Statement stmt = null;
	
	public SOCFetchInstallBase(String logFileName, SOCFileWriter fileWriter)
	{
		d_logFileName = logFileName;
		d_fileWriter = fileWriter;
	}
	
	public void fetchInstallBaseData() throws SOCException
	{
		populateInstallBaseMasterTable();
		populateInstallBaseAddresses();
		populateInstallBaseContacts();
		populateInstallBaseNotes();
		populateInstallBaseWarranty();
	}
	
	private void populateInstallBaseMasterTable() throws SOCException
	{
		String activeInstallBaseQuery = QueryReader.getQuery(QueryConstants.GET_ACTIVE_INSTALL_BASE_QUERY);
        	
    	try 
    	{
    		d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(SOCConstants.MESSAGE_POPULATION_TBL_STAGING_INSTALL_BASE_INFO_STARTS));
    		cs = DBConnection.getConnection().prepareCall(activeInstallBaseQuery);
    	    boolean result = cs.execute();
    	    int recordsUpdated=0;
    	    if(!result)
    	    {
    	    	recordsUpdated = cs.getUpdateCount();
    	    }

			if(recordsUpdated==0)
			{
				d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(ErrorMessageReader.getError(
        				ErrorConstants.NO_RECORD_POPULATION_TBL_STAGING_INSTALL_BASE_INFO)));
				throw new SOCException(
        		ErrorMessageReader.getError(
        				ErrorConstants.NO_RECORD_POPULATION_TBL_STAGING_INSTALL_BASE_INFO));
			}
			
			d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(SOCConstants.MESSAGE_POPULATION_TBL_STAGING_INSTALL_BASE_INFO_ENDS + recordsUpdated));
			
			
		} catch (SQLException se) {
			d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(ErrorMessageReader.getError(
    				ErrorConstants.FAILED_DATA_POPULATION_TBL_STAGING_INSTALL_BASE_INFO)+SOCConstants.NEW_LINE+se.getMessage()));
			throw new SOCException(
            		ErrorMessageReader.getError(
            				ErrorConstants.FAILED_DATA_POPULATION_TBL_STAGING_INSTALL_BASE_INFO),se);
		}
		analyzeInstallBaseMasterTable();
	}
		
	private void analyzeInstallBaseMasterTable() throws SOCException
	{
		String installBaseAnalyzeQuery = QueryReader.getQuery(QueryConstants.ANALYZE_ACTIVE_INSTALL_BASE_QUERY);
    	
    	try 
    	{
    		d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(SOCConstants.MESSAGE_ANALYZE_INSTALL_BASE_STARTS));
    		stmt = DBConnection.getConnection().createStatement();
    	    stmt.execute(installBaseAnalyzeQuery);
    	    			
			d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(SOCConstants.MESSAGE_ANALYZE_INSTALL_BASE_ENDS));
			
			
		} catch (SQLException se) {
			d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(ErrorMessageReader.getError(
    				ErrorConstants.FAILED_ANALYZE_TBL_STAGING_INSTALL_BASE_INFO)+SOCConstants.NEW_LINE+se.getMessage()));
			throw new SOCException(
            		ErrorMessageReader.getError(
            				ErrorConstants.FAILED_ANALYZE_TBL_STAGING_INSTALL_BASE_INFO),se);
		}
	}
	
	private void populateInstallBaseAddresses() throws SOCException
	{
		String addressInstallBaseQuery = QueryReader.getQuery(QueryConstants.GET_ADDRESS_INSTALL_BASE_QUERY);
    	
    	try 
    	{
    		d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(SOCConstants.MESSAGE_POPULATION_TBL_STAGING_INSTALL_BASE_ADDR_STARTS));
    		cs = DBConnection.getConnection().prepareCall(addressInstallBaseQuery);
    	    boolean result = cs.execute();
    	    int recordsUpdated=0;
    	    if(!result)
    	    {
    	    	recordsUpdated = cs.getUpdateCount();
    	    }

			if(recordsUpdated==0)
			{
				d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(ErrorMessageReader.getError(
        				ErrorConstants.NO_RECORD_POPULATION_TBL_STAGING_INSTALL_BASE_ADDR)));
				throw new SOCException(
        		ErrorMessageReader.getError(
        				ErrorConstants.NO_RECORD_POPULATION_TBL_STAGING_INSTALL_BASE_ADDR));
			}
			
			d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(SOCConstants.MESSAGE_POPULATION_TBL_STAGING_INSTALL_BASE_ADDR_ENDS + recordsUpdated));
			
			
		} catch (SQLException se) {
			d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(ErrorMessageReader.getError(
    				ErrorConstants.FAILED_DATA_POPULATION_TBL_STAGING_INSTALL_BASE_ADDR)+SOCConstants.NEW_LINE+se.getMessage()));
			throw new SOCException(
            		ErrorMessageReader.getError(
            				ErrorConstants.FAILED_DATA_POPULATION_TBL_STAGING_INSTALL_BASE_ADDR),se);
		}
		analyzeInstallBaseAddresses();
	}
	
	private void analyzeInstallBaseAddresses() throws SOCException
	{
		String addressAnalyzeQuery = QueryReader.getQuery(QueryConstants.ANALYZE_ADDRESS_INSTALL_BASE_QUERY);
    	
    	try 
    	{
    		d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(SOCConstants.MESSAGE_ANALYZE_ADDRESS_INSTALL_BASE_STARTS));
    		stmt = DBConnection.getConnection().createStatement();
    	    stmt.execute(addressAnalyzeQuery);
    	    			
			d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(SOCConstants.MESSAGE_ANALYZE_ADDRESS_INSTALL_BASE_ENDS ));
			
			
		} catch (SQLException se) {
			d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(ErrorMessageReader.getError(
    				ErrorConstants.FAILED_ANALYZE_TBL_STAGING_INSTALL_BASE_ADDR)+SOCConstants.NEW_LINE+se.getMessage()));
			throw new SOCException(
            		ErrorMessageReader.getError(
            				ErrorConstants.FAILED_ANALYZE_TBL_STAGING_INSTALL_BASE_ADDR),se);
		}
	}
	
	private void populateInstallBaseContacts() throws SOCException
	{
		String contactsInstallBaseQuery = QueryReader.getQuery(QueryConstants.GET_CONTACTS_INSTALL_BASE_QUERY);
    	
    	try 
    	{
    		d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(SOCConstants.MESSAGE_POPULATION_TBL_STAGING_INSTALL_BASE_CONT_STARTS));
    		cs = DBConnection.getConnection().prepareCall(contactsInstallBaseQuery);
    	    boolean result = cs.execute();
    	    int recordsUpdated=0;
    	    if(!result)
    	    {
    	    	recordsUpdated = cs.getUpdateCount();
    	    }

			if(recordsUpdated==0)
			{
				d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(ErrorMessageReader.getError(
        				ErrorConstants.NO_RECORD_POPULATION_TBL_STAGING_INSTALL_BASE_CONT)));
				throw new SOCException(
        		ErrorMessageReader.getError(
        				ErrorConstants.NO_RECORD_POPULATION_TBL_STAGING_INSTALL_BASE_CONT));
			}
			
			d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(SOCConstants.MESSAGE_POPULATION_TBL_STAGING_INSTALL_BASE_CONT_ENDS + recordsUpdated));
			
			
		} catch (SQLException se) {
			d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(ErrorMessageReader.getError(
    				ErrorConstants.FAILED_DATA_POPULATION_TBL_STAGING_INSTALL_BASE_CONT)+SOCConstants.NEW_LINE+se.getMessage()));
			throw new SOCException(
            		ErrorMessageReader.getError(
            				ErrorConstants.FAILED_DATA_POPULATION_TBL_STAGING_INSTALL_BASE_CONT),se);
		}
		
		analyzeInstallBasecOontacts();
	}
	
	private void analyzeInstallBasecOontacts() throws SOCException
	{
		String contactsAnalyzeQuery = QueryReader.getQuery(QueryConstants.ANALYZE_CONTACTS_INSTALL_BASE_QUERY);
    	
    	try 
    	{
    		d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(SOCConstants.MESSAGE_ANALYZE_CONTACTS_INSTALL_BASE_STARTS));
    		stmt =DBConnection.getConnection().createStatement();
    	    stmt.execute(contactsAnalyzeQuery);
    	    			
			d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(SOCConstants.MESSAGE_ANALYZE_CONTACTS_INSTALL_BASE_ENDS ));
			
			
		} catch (SQLException se) {
			d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(ErrorMessageReader.getError(
    				ErrorConstants.FAILED_ANALYZE_TBL_STAGING_INSTALL_BASE_CONT)+SOCConstants.NEW_LINE+se.getMessage()));
			throw new SOCException(
            		ErrorMessageReader.getError(
            				ErrorConstants.FAILED_ANALYZE_TBL_STAGING_INSTALL_BASE_CONT),se);
		}
	}
	
	
	private void populateInstallBaseNotes() throws SOCException
	{
		String notesInstallBaseQuery = QueryReader.getQuery(QueryConstants.GET_NOTES_INSTALL_BASE_QUERY);
    	
    	try 
    	{
    		d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(SOCConstants.MESSAGE_POPULATION_TBL_STAGING_INSTALL_BASE_NOTES_STARTS));
    		cs = DBConnection.getConnection().prepareCall(notesInstallBaseQuery);
    	    boolean result = cs.execute();
    	    int recordsUpdated=0;
    	    if(!result)
    	    {
    	    	recordsUpdated = cs.getUpdateCount();
    	    }

			if(recordsUpdated==0)
			{
				d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(ErrorMessageReader.getError(
        				ErrorConstants.NO_RECORD_POPULATION_TBL_STAGING_INSTALL_BASE_NOTES)));
				throw new SOCException(
        		ErrorMessageReader.getError(
        				ErrorConstants.NO_RECORD_POPULATION_TBL_STAGING_INSTALL_BASE_NOTES));
			}
			
			d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(SOCConstants.MESSAGE_POPULATION_TBL_STAGING_INSTALL_BASE_NOTES_ENDS + recordsUpdated));
			
			
		} catch (SQLException se) {
			d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(ErrorMessageReader.getError(
    				ErrorConstants.FAILED_DATA_POPULATION_TBL_STAGING_INSTALL_BASE_NOTES)+SOCConstants.NEW_LINE+se.getMessage()));
			throw new SOCException(
            		ErrorMessageReader.getError(
            				ErrorConstants.FAILED_DATA_POPULATION_TBL_STAGING_INSTALL_BASE_NOTES),se);
		}
		analyzeInstallBasecNotes();
	}
	
	private void analyzeInstallBasecNotes() throws SOCException
	{
		String notesAnalyzeQuery = QueryReader.getQuery(QueryConstants.ANALYZE_NOTES_INSTALL_BASE_QUERY);
    	
    	try 
    	{
    		d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(SOCConstants.MESSAGE_ANALYZE_NOTES_INSTALL_BASE_STARTS));
    		stmt = DBConnection.getConnection().createStatement();
    	    stmt.execute(notesAnalyzeQuery);
    	    			
			d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(SOCConstants.MESSAGE_ANALYZE_NOTES_INSTALL_BASE_ENDS));
			
			
		} catch (SQLException se) {
			d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(ErrorMessageReader.getError(
    				ErrorConstants.FAILED_ANALYZE_TBL_STAGING_INSTALL_BASE_NOTES)+SOCConstants.NEW_LINE+se.getMessage()));
			throw new SOCException(
            		ErrorMessageReader.getError(
            				ErrorConstants.FAILED_ANALYZE_TBL_STAGING_INSTALL_BASE_NOTES),se);
		}
	}
	
	private void populateInstallBaseWarranty() throws SOCException
	{
		String warrantyInstallBaseQuery = QueryReader.getQuery(QueryConstants.GET_WARRANTY_INSTALL_BASE_QUERY);
    	
    	try 
    	{
    		d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(SOCConstants.MESSAGE_POPULATION_TBL_STAGING_INSTALL_BASE_WTY_STARTS));
    		cs = DBConnection.getConnection().prepareCall(warrantyInstallBaseQuery);
    	    boolean result = cs.execute();
    	    int recordsUpdated=0;
    	    if(!result)
    	    {
    	    	recordsUpdated = cs.getUpdateCount();
    	    }

			if(recordsUpdated==0)
			{
				d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(ErrorMessageReader.getError(
        				ErrorConstants.NO_RECORD_POPULATION_TBL_STAGING_INSTALL_BASE_WTY)));
				throw new SOCException(
        		ErrorMessageReader.getError(
        				ErrorConstants.NO_RECORD_POPULATION_TBL_STAGING_INSTALL_BASE_WTY));
			}
			
			d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(SOCConstants.MESSAGE_POPULATION_TBL_STAGING_INSTALL_BASE_WTY_ENDS + recordsUpdated));
			
			
		} catch (SQLException se) {
			d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(ErrorMessageReader.getError(
    				ErrorConstants.FAILED_DATA_POPULATION_TBL_STAGING_INSTALL_BASE_WTY)+SOCConstants.NEW_LINE+se.getMessage()));
			throw new SOCException(
            		ErrorMessageReader.getError(
            				ErrorConstants.FAILED_DATA_POPULATION_TBL_STAGING_INSTALL_BASE_WTY),se);
		}
		analyzeInstallBasecWarranty();
	}
	
	private void analyzeInstallBasecWarranty() throws SOCException
	{
		String warrantyAnalyzeQuery = QueryReader.getQuery(QueryConstants.ANALYZE_WARRANTY_INSTALL_BASE_QUERY);
    	
    	try 
    	{
    		d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(SOCConstants.MESSAGE_ANALYZE_WARRANTY_INSTALL_BASE_STARTS));
    		stmt = DBConnection.getConnection().createStatement();
    	    stmt.execute(warrantyAnalyzeQuery);
    	    			
			d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(SOCConstants.MESSAGE_ANALYZE_WARRANTY_INSTALL_BASE_ENDS));
			
			
		} catch (SQLException se) {
			d_fileWriter.write(d_logFileName, SOCUtility.writeLogMessage(ErrorMessageReader.getError(
    				ErrorConstants.FAILED_ANALYZE_TBL_STAGING_INSTALL_BASE_WTY)+SOCConstants.NEW_LINE+se.getMessage()));
			throw new SOCException(
            		ErrorMessageReader.getError(
            				ErrorConstants.FAILED_ANALYZE_TBL_STAGING_INSTALL_BASE_WTY),se);
		}
	}
}
